﻿using System;
using System.Data;
using System.Data.SqlClient;
using AccessControl.Common;


namespace AccessControl.DataAcess
{
	public class GroupUserAccess
	{
		public void Insert(AccessControlDS.GroupUserRow row)
		{
			using (var cn = new SqlConnection(Util.ConnectionString))
			{
				var cmdInsert = new SqlCommand("SP_GroupUser_INS", cn) { CommandType = CommandType.StoredProcedure };
				cmdInsert.Parameters.AddWithValue("@Username", row.Username);
				cmdInsert.Parameters.AddWithValue("@Password", row.Password);
				cmdInsert.Parameters.AddWithValue("@FullName", row.FullName);
				try
				{
					cn.Open();
					cmdInsert.ExecuteNonQuery();
				}
				finally
				{
					cn.Close();
				}
			}
		}

		public AccessControlDS.GroupUserDataTable SelectAll()
		{
			using (var cn = new SqlConnection(Util.ConnectionString))
			{
				var cmdSelect = new SqlCommand("SELECT * FROM GroupUser", cn);
				var userTable = new AccessControlDS.GroupUserDataTable();
				try
				{
					cn.Open();
					var reader = cmdSelect.ExecuteReader();
					if (reader != null)
						while (reader.Read())
						{
							var newRow = userTable.NewGroupUserRow();
							newRow.ID = (int)reader["ID"];
							newRow.Username = (String)reader["Username"];
							newRow.Password = (String)reader["Password"];
							newRow.FullName= (String)reader["FullName"];
							userTable.AddGroupUserRow(newRow);
						}
				}
				finally
				{
					cn.Close();
				}
				return userTable.Rows.Count != 0 ? userTable : null;
			}
		}


		public AccessControlDS.GroupUserDataTable SelectByFilter(string filter)
		{
			using (var cn = new SqlConnection(Util.ConnectionString))
			{
				var cmdSelect = new SqlCommand(String.Format("SELECT * FROM GroupUser WHERE {0}", filter), cn);
				var userTable = new AccessControlDS.GroupUserDataTable();
				try
				{
					cn.Open();
					var reader = cmdSelect.ExecuteReader();
					if (reader != null)
						while (reader.Read())
						{
							var newRow = userTable.NewGroupUserRow();
							newRow.ID = (int)reader["ID"];
							newRow.Username = (String)reader["Username"];
							newRow.Password = (String)reader["Password"];
							newRow.FullName= (String)reader["FullName"];
							userTable.AddGroupUserRow(newRow);
						}
				}
				finally
				{
					cn.Close();
				}
				return userTable.Rows.Count != 0 ? userTable : null;
			}
		}
	}
}
